-- @owner: @nanyang12
-- @date: 2024/10/23
-- @testpoint:start with connect by使用子查询语句
--step1:建表并插入数据 
drop table if exists t_connect_by_0026_01,t_connect_by_0026_02; 
create table t_connect_by_0026_01(id int,pid int); 
create table t_connect_by_0026_02(id int,pid int); 
insert into t_connect_by_0026_01 values(generate_series(1,100),generate_series(1,100)); 
insert into t_connect_by_0026_02 values(generate_series(51,100),generate_series(1,100)); 
--step2:start with connect by使用子查询语句 
select t1.id,t1.pid,t2.id,t2.pid from t_connect_by_0026_01 t1 join t_connect_by_0026_02 t2 on t1.id=t2.id where exists(select id from t_connect_by_0026_02 start 
with id=1 connect by prior id=pid and t1.id=id) start with t1.id=141 connect by prior t2.pid=t1.id; 
--step3:清理环境 
drop table t_connect_by_0026_01; 
drop table t_connect_by_0026_02;
